Date: July 14, 2022
import logging
def configure_root_logger():
console_logger = logging.StreamHandler()
console_logger.setLevel(logging.DEBUG)
console_logger.setFormatter(
logging.Formatter("%(asctime)s - %(levelname)s - %(message)s")
)
logger = logging.getLogger()
logger.handlers = [] # reset handlers in case this cell is re-run
logger.setLevel(logging.DEBUG)
logger.addHandler(console_logger)
return logger
import os
from sqlalchemy import create_engine
from dash import Dash, html, dcc
import plotly.express as px
import pandas as pd
import plotly
plotly.offline.init_notebook_mode()
logger = configure_root_logger()
pg_engine = create_engine("postgresql://postgres@127.0.0.1:54344/traffic_stops_nc")
pg_conn = pg_engine.connect()
df = pd.read_sql(
f"""
SELECT
id
, name
FROM nc_agency
WHERE name ~ 'Mecklenburg County'
""",
pg_conn,
)
agency_id = df.iloc[0]['id']
df
| id | name | |
|---|---|---|
| 0 | 168 | Mecklenburg County Sheriff's Office |
df = pd.read_sql(
f"""
SELECT
MIN(date)::date AS first_reported_stop
, MAX(date)::date AS last_reported_stop
, COUNT(*) AS total_stops
FROM nc_stop
WHERE nc_stop.agency_id = {agency_id}
""",
pg_conn,
)
df.T
| 0 | |
|---|---|
| first_reported_stop | 2002-01-01 |
| last_reported_stop | 2022-07-13 |
| total_stops | 41952 |
The Mecklenburg County Sheriff's Office made about 40k stops in the past ~20 years.
mecklenburg_driver_stops = f"""
SELECT
"nc_stop"."stop_id"
, "nc_stop"."date"
, DATE_TRUNC('year', date AT TIME ZONE 'America/New_York') AS "year"
, (CASE WHEN nc_person.ethnicity = 'H' THEN 'Hispanic'
WHEN nc_person.ethnicity = 'N' AND nc_person.race = 'A' THEN 'Asian'
WHEN nc_person.ethnicity = 'N' AND nc_person.race = 'B' THEN 'Black'
WHEN nc_person.ethnicity = 'N' AND nc_person.race = 'I' THEN 'Native American'
WHEN nc_person.ethnicity = 'N' AND nc_person.race = 'U' THEN 'Other'
WHEN nc_person.ethnicity = 'N' AND nc_person.race = 'W' THEN 'White'
END) as driver_race
, "nc_stop"."officer_id"
, (CASE WHEN nc_stop.purpose = 1 THEN 'Speed Limit Violation' -- "Safety stop"
WHEN nc_stop.purpose = 2 THEN 'Stop Light/Sign Violation' -- "Safety stop"
WHEN nc_stop.purpose = 3 THEN 'Driving While Impaired' -- "Safety stop"
WHEN nc_stop.purpose = 4 THEN 'Safe Movement Violation' -- "Safety stop"
WHEN nc_stop.purpose = 8 THEN 'Investigation' -- "Investigatory stop"
WHEN nc_stop.purpose = 7 THEN 'Seat Belt Violation' -- "Investigatory stop"
WHEN nc_stop.purpose = 10 THEN 'Checkpoint' -- "Investigatory stop"
WHEN nc_stop.purpose = 5 THEN 'Vehicle Equipment Violation' -- "Economic stop"
WHEN nc_stop.purpose = 6 THEN 'Vehicle Regulatory Violation' -- "Economic stop"
WHEN nc_stop.purpose = 9 THEN 'Other Motor Vehicle Violation' -- "Economic stop"
END) as stop_purpose
, (CASE WHEN nc_stop.purpose IN (1, 2, 3, 4) THEN 'Safety Violation'
WHEN nc_stop.purpose IN (7, 8, 10) THEN 'Investigatory'
WHEN nc_stop.purpose IN (5, 6, 9) THEN 'Regulatory and Equipment'
ELSE 'Other'
END) as stop_purpose_group
, (nc_search.search_id IS NOT NULL) AS driver_searched
, "nc_search"."search_id"
, (CASE WHEN nc_search.type = 1 THEN 'Consent'
WHEN nc_search.type = 2 THEN 'Search Warrant'
WHEN nc_search.type = 3 THEN 'Probable Cause'
WHEN nc_search.type = 4 THEN 'Search Incident to Arrest'
WHEN nc_search.type = 5 THEN 'Protective Frisk'
ELSE ''
END) as search_type
, (CASE
WHEN nc_contraband.contraband_id IS NULL THEN false
ELSE true
END) AS contraband_found
, nc_contraband.contraband_id
, (CASE WHEN nc_contraband.ounces > 0 OR nc_contraband.pounds > 0 OR nc_contraband.dosages > 0 OR nc_contraband.grams > 0 OR nc_contraband.kilos > 0 THEN true
ELSE false
END) AS drugs_found
, (CASE WHEN nc_contraband.pints > 0 OR nc_contraband.gallons > 0 THEN true
ELSE false
END) AS alcohol_found
, (CASE WHEN nc_contraband.weapons > 0 THEN true
ELSE false
END) AS weapons_found
, (CASE WHEN nc_contraband.money > 0 THEN true
ELSE false
END) AS money_found
, (CASE WHEN nc_contraband.dollar_amount > 0 THEN true
ELSE false
END) AS other_found
, nc_contraband.ounces AS contraband_ounces
, nc_contraband.pounds AS contraband_pounds
, nc_contraband.dosages AS contraband_dosages
, nc_contraband.grams AS contraband_grams
, nc_contraband.kilos AS contraband_kilos
, nc_contraband.pints AS contraband_pints
, nc_contraband.gallons AS contraband_gallons
, nc_contraband.weapons AS contraband_weapons
, nc_contraband.money AS contraband_money
, nc_contraband.dollar_amount AS contraband_dollar_amount
FROM "nc_stop"
INNER JOIN "nc_person"
ON ("nc_stop"."stop_id" = "nc_person"."stop_id" AND "nc_person"."type" = 'D')
LEFT OUTER JOIN "nc_search"
ON ("nc_stop"."stop_id" = "nc_search"."stop_id")
LEFT OUTER JOIN "nc_contraband"
ON ("nc_stop"."stop_id" = "nc_contraband"."stop_id")
WHERE
nc_stop.agency_id = {agency_id}
ORDER BY nc_stop.date ASC
"""
df = pd.read_sql(
f"""
WITH stops AS ({mecklenburg_driver_stops})
SELECT
driver_searched
, COUNT(*) AS stop_count
FROM stops
GROUP BY 1
ORDER BY 1
""",
pg_conn,
)
df
| driver_searched | stop_count | |
|---|---|---|
| 0 | False | 41597 |
| 1 | True | 355 |
Drivers were searched in 355 of the ~42k total stops.
df = pd.read_sql(
f"""
WITH stops AS ({mecklenburg_driver_stops})
SELECT
contraband_found
, COUNT(*) AS stop_count
FROM stops
GROUP BY 1
ORDER BY 1
""",
pg_conn,
)
df
| contraband_found | stop_count | |
|---|---|---|
| 0 | False | 41845 |
| 1 | True | 107 |
Contraband was found in 107 of the ~42k total stops.
df = pd.read_sql(
f"""
WITH stops AS ({mecklenburg_driver_stops})
SELECT
drugs_found
, alcohol_found
, weapons_found
, money_found
, other_found
, COUNT(*)
FROM stops
WHERE contraband_found = true
GROUP BY 1, 2, 3, 4, 5
ORDER BY 1, 2, 3, 4, 5
""",
pg_conn,
)
pd.concat([df, pd.DataFrame([{"count": df["count"].sum()}])], ignore_index=True)
| drugs_found | alcohol_found | weapons_found | money_found | other_found | count | |
|---|---|---|---|---|---|---|
| 0 | False | False | False | False | False | 26 |
| 1 | False | False | False | False | True | 5 |
| 2 | False | False | False | True | False | 2 |
| 3 | False | False | True | False | False | 11 |
| 4 | False | False | True | True | False | 1 |
| 5 | False | True | False | False | False | 14 |
| 6 | True | False | False | False | False | 20 |
| 7 | True | False | False | True | False | 1 |
| 8 | True | False | True | False | False | 1 |
| 9 | True | False | True | True | False | 1 |
| 10 | True | True | False | False | False | 20 |
| 11 | True | True | True | False | False | 2 |
| 12 | True | True | True | True | False | 3 |
| 13 | NaN | NaN | NaN | NaN | NaN | 107 |
Multiple contraband seizures may occur during the same stop (e.g. both drugs and alcohol found).
df = pd.read_sql(
f"""
WITH stops AS ({mecklenburg_driver_stops})
SELECT
stop_purpose_group
, driver_race
, COUNT(*) FILTER (WHERE drugs_found = true) AS drugs_found
, COUNT(*) FILTER (WHERE alcohol_found = true) AS alcohol_found
, COUNT(*) FILTER (WHERE weapons_found = true) AS weapons_found
, COUNT(*) FILTER (WHERE money_found = true) AS money_found
, COUNT(*) FILTER (WHERE other_found = true) AS other_found
, COUNT(*) FILTER (WHERE driver_searched = true) AS drivers_searched
, COUNT(*) AS total_stops
FROM stops
GROUP BY 1, 2
ORDER BY 1, 2
""",
pg_conn,
)
pd.concat([df, df[["drugs_found", "alcohol_found", "weapons_found", "money_found", "other_found", "drivers_searched", "total_stops"]].sum().to_frame().T], ignore_index=True)
| stop_purpose_group | driver_race | drugs_found | alcohol_found | weapons_found | money_found | other_found | drivers_searched | total_stops | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | Investigatory | Asian | 0 | 0 | 0 | 0 | 0 | 0 | 8 |
| 1 | Investigatory | Black | 6 | 3 | 1 | 1 | 1 | 32 | 525 |
| 2 | Investigatory | Hispanic | 0 | 0 | 0 | 0 | 0 | 2 | 143 |
| 3 | Investigatory | Native American | 0 | 0 | 0 | 0 | 0 | 0 | 6 |
| 4 | Investigatory | Other | 0 | 0 | 0 | 0 | 0 | 2 | 27 |
| 5 | Investigatory | White | 4 | 2 | 0 | 0 | 0 | 12 | 327 |
| 6 | Regulatory and Equipment | Asian | 0 | 0 | 0 | 0 | 0 | 0 | 96 |
| 7 | Regulatory and Equipment | Black | 3 | 1 | 4 | 4 | 1 | 52 | 3890 |
| 8 | Regulatory and Equipment | Hispanic | 1 | 0 | 0 | 0 | 0 | 15 | 732 |
| 9 | Regulatory and Equipment | Native American | 0 | 0 | 0 | 0 | 0 | 2 | 57 |
| 10 | Regulatory and Equipment | Other | 0 | 0 | 0 | 0 | 0 | 0 | 150 |
| 11 | Regulatory and Equipment | White | 3 | 1 | 1 | 1 | 2 | 19 | 2215 |
| 12 | Safety Violation | Asian | 0 | 0 | 0 | 0 | 0 | 1 | 753 |
| 13 | Safety Violation | Black | 20 | 18 | 8 | 1 | 1 | 110 | 15528 |
| 14 | Safety Violation | Hispanic | 4 | 5 | 3 | 1 | 0 | 41 | 4179 |
| 15 | Safety Violation | Native American | 0 | 0 | 0 | 0 | 0 | 1 | 200 |
| 16 | Safety Violation | Other | 0 | 0 | 0 | 0 | 0 | 4 | 996 |
| 17 | Safety Violation | White | 7 | 9 | 2 | 0 | 0 | 62 | 12120 |
| 18 | NaN | NaN | 48 | 39 | 19 | 8 | 5 | 355 | 41952 |
Breakdown of contraband seizures by race and stop purpose grouping.
df = pd.read_sql(
f"""
WITH stops AS ({mecklenburg_driver_stops})
SELECT
year
, stop_purpose_group
, driver_race
, COUNT(*) FILTER (WHERE drugs_found = true) AS drugs_found
, COUNT(*) FILTER (WHERE alcohol_found = true) AS alcohol_found
, COUNT(*) FILTER (WHERE weapons_found = true) AS weapons_found
, COUNT(*) FILTER (WHERE money_found = true) AS money_found
, COUNT(*) FILTER (WHERE other_found = true) AS other_found
, COUNT(*) FILTER (WHERE driver_searched = true) AS drivers_searched
, COUNT(*) AS total_stops
FROM stops
WHERE year >= '2020-01-01'
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3
""",
pg_conn,
)
pd.concat([df, df[["drugs_found", "alcohol_found", "weapons_found", "money_found", "other_found", "drivers_searched", "total_stops"]].sum().to_frame().T], ignore_index=True)
| year | stop_purpose_group | driver_race | drugs_found | alcohol_found | weapons_found | money_found | other_found | drivers_searched | total_stops | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2020-01-01 | Investigatory | Black | 1 | 1 | 0 | 0 | 0 | 2 | 48 |
| 1 | 2020-01-01 | Investigatory | Hispanic | 0 | 0 | 0 | 0 | 0 | 0 | 10 |
| 2 | 2020-01-01 | Investigatory | Native American | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 3 | 2020-01-01 | Investigatory | Other | 0 | 0 | 0 | 0 | 0 | 0 | 2 |
| 4 | 2020-01-01 | Investigatory | White | 0 | 0 | 0 | 0 | 0 | 0 | 24 |
| 5 | 2020-01-01 | Regulatory and Equipment | Asian | 0 | 0 | 0 | 0 | 0 | 0 | 3 |
| 6 | 2020-01-01 | Regulatory and Equipment | Black | 0 | 0 | 0 | 0 | 0 | 0 | 340 |
| 7 | 2020-01-01 | Regulatory and Equipment | Hispanic | 0 | 0 | 0 | 0 | 0 | 0 | 50 |
| 8 | 2020-01-01 | Regulatory and Equipment | Native American | 0 | 0 | 0 | 0 | 0 | 0 | 4 |
| 9 | 2020-01-01 | Regulatory and Equipment | Other | 0 | 0 | 0 | 0 | 0 | 0 | 6 |
| 10 | 2020-01-01 | Regulatory and Equipment | White | 0 | 0 | 0 | 0 | 0 | 0 | 146 |
| 11 | 2020-01-01 | Safety Violation | Asian | 0 | 0 | 0 | 0 | 0 | 0 | 102 |
| 12 | 2020-01-01 | Safety Violation | Black | 2 | 1 | 1 | 0 | 0 | 4 | 2633 |
| 13 | 2020-01-01 | Safety Violation | Hispanic | 0 | 0 | 0 | 0 | 0 | 2 | 692 |
| 14 | 2020-01-01 | Safety Violation | Native American | 0 | 0 | 0 | 0 | 0 | 0 | 37 |
| 15 | 2020-01-01 | Safety Violation | Other | 0 | 0 | 0 | 0 | 0 | 0 | 147 |
| 16 | 2020-01-01 | Safety Violation | White | 3 | 1 | 0 | 0 | 0 | 3 | 1995 |
| 17 | 2021-01-01 | Investigatory | Black | 0 | 0 | 0 | 0 | 0 | 1 | 82 |
| 18 | 2021-01-01 | Investigatory | Hispanic | 0 | 0 | 0 | 0 | 0 | 0 | 38 |
| 19 | 2021-01-01 | Investigatory | Other | 0 | 0 | 0 | 0 | 0 | 0 | 6 |
| 20 | 2021-01-01 | Investigatory | White | 1 | 1 | 0 | 0 | 0 | 1 | 32 |
| 21 | 2021-01-01 | Regulatory and Equipment | Asian | 0 | 0 | 0 | 0 | 0 | 0 | 6 |
| 22 | 2021-01-01 | Regulatory and Equipment | Black | 0 | 0 | 0 | 0 | 0 | 0 | 440 |
| 23 | 2021-01-01 | Regulatory and Equipment | Hispanic | 1 | 0 | 0 | 0 | 0 | 1 | 120 |
| 24 | 2021-01-01 | Regulatory and Equipment | Native American | 0 | 0 | 0 | 0 | 0 | 0 | 4 |
| 25 | 2021-01-01 | Regulatory and Equipment | Other | 0 | 0 | 0 | 0 | 0 | 0 | 22 |
| 26 | 2021-01-01 | Regulatory and Equipment | White | 0 | 0 | 0 | 0 | 0 | 0 | 247 |
| 27 | 2021-01-01 | Safety Violation | Asian | 0 | 0 | 0 | 0 | 0 | 0 | 144 |
| 28 | 2021-01-01 | Safety Violation | Black | 5 | 2 | 1 | 0 | 0 | 10 | 3457 |
| 29 | 2021-01-01 | Safety Violation | Hispanic | 0 | 0 | 1 | 0 | 0 | 2 | 1089 |
| 30 | 2021-01-01 | Safety Violation | Native American | 0 | 0 | 0 | 0 | 0 | 0 | 33 |
| 31 | 2021-01-01 | Safety Violation | Other | 0 | 0 | 0 | 0 | 0 | 0 | 236 |
| 32 | 2021-01-01 | Safety Violation | White | 1 | 0 | 1 | 0 | 0 | 2 | 2328 |
| 33 | 2022-01-01 | Investigatory | Asian | 0 | 0 | 0 | 0 | 0 | 0 | 2 |
| 34 | 2022-01-01 | Investigatory | Black | 0 | 0 | 0 | 0 | 0 | 1 | 38 |
| 35 | 2022-01-01 | Investigatory | Hispanic | 0 | 0 | 0 | 0 | 0 | 0 | 8 |
| 36 | 2022-01-01 | Investigatory | Other | 0 | 0 | 0 | 0 | 0 | 0 | 2 |
| 37 | 2022-01-01 | Investigatory | White | 0 | 0 | 0 | 0 | 0 | 0 | 7 |
| 38 | 2022-01-01 | Regulatory and Equipment | Asian | 0 | 0 | 0 | 0 | 0 | 0 | 5 |
| 39 | 2022-01-01 | Regulatory and Equipment | Black | 0 | 0 | 0 | 0 | 0 | 1 | 220 |
| 40 | 2022-01-01 | Regulatory and Equipment | Hispanic | 0 | 0 | 0 | 0 | 0 | 0 | 38 |
| 41 | 2022-01-01 | Regulatory and Equipment | Native American | 0 | 0 | 0 | 0 | 0 | 0 | 2 |
| 42 | 2022-01-01 | Regulatory and Equipment | Other | 0 | 0 | 0 | 0 | 0 | 0 | 13 |
| 43 | 2022-01-01 | Regulatory and Equipment | White | 0 | 0 | 0 | 0 | 0 | 0 | 124 |
| 44 | 2022-01-01 | Safety Violation | Asian | 0 | 0 | 0 | 0 | 0 | 0 | 59 |
| 45 | 2022-01-01 | Safety Violation | Black | 2 | 0 | 1 | 1 | 0 | 6 | 1053 |
| 46 | 2022-01-01 | Safety Violation | Hispanic | 1 | 1 | 1 | 1 | 0 | 1 | 303 |
| 47 | 2022-01-01 | Safety Violation | Native American | 0 | 0 | 0 | 0 | 0 | 0 | 5 |
| 48 | 2022-01-01 | Safety Violation | Other | 0 | 0 | 0 | 0 | 0 | 1 | 74 |
| 49 | 2022-01-01 | Safety Violation | White | 0 | 0 | 0 | 0 | 0 | 0 | 636 |
| 50 | NaT | NaN | NaN | 17 | 7 | 6 | 2 | 0 | 38 | 17113 |
Breakdown of contraband seizures by race and stop purpose grouping since 2020.
df = pd.read_sql(
f"""
WITH stops AS ({mecklenburg_driver_stops})
SELECT
*
FROM stops
""",
pg_conn,
)
df.to_csv("mecklenburg-sheriff-2022-07.csv", index=False)